package com.duleme.dao;

import com.duleme.common.DBUtils;
import com.duleme.entity.Goods;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class GoodsDao {
    public boolean insertGoods(Goods goods){
        String sql = "INSERT INTO shopgoods (bookId,bookName,goodsImage,goodsName,shopId,remain,price,sell) VALUES (?,?,?,?,?,?,?,?)";
        try{
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1,goods.getBookId());
            ps.setString(2,goods.getGoodsName());
            ps.setString(3,goods.getGoodsImage());
            ps.setString(4,goods.getGoodsName());
            ps.setInt(5,goods.getShopId());
            ps.setInt(6,goods.getRemain());
            ps.setDouble(7,goods.getPrice());
            ps.setInt(8,goods.getSell());
            if(ps.executeUpdate()>0){
                DBUtils.close(connection,ps,null);
                return true;
            }
            DBUtils.close(connection,ps,null);
            return false;
        }catch (Exception e){
            e.getStackTrace();
            return false;
        }
    }

    public List<Goods> findAllBooksInShop(String shopName){
        String sql="SELECT * FROM shopgoods WHERE shopName=?";
        try {
            List<Goods> goodsList=new ArrayList<Goods>();
            Connection connection= DBUtils.getConnection();
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.setString(1,shopName);
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                Goods goods=new Goods();
                goods.setBookId(rs.getInt("bookId"));
                goods.setBookName(rs.getString("bookName"));
                goods.setPrice(rs.getDouble("price"));
                goods.setRemain(rs.getInt("remain"));
                goods.setSell(rs.getInt("sell"));
                goods.setGoodsImage(rs.getString("goodsImage"));
                goods.setGoodsName(rs.getString("goodsName"));
                goods.setGoodsId(rs.getInt("goodsId"));
                goods.setShopName(rs.getString("shopName"));
                goods.setShopId(rs.getInt("shopId"));
                goodsList.add(goods);
            }
            DBUtils.close(connection,ps,rs);
            return goodsList;

        }catch (Exception e){
            e.getStackTrace();
            return null;
        }
    }
    public List<Goods> findGoodsById(int id){
        List<Goods> list=new ArrayList<>();
        String sql="SELECT * FROM shopgoods WHERE shopId=?";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1,id);
            ResultSet rs=ps.executeQuery();
            while (rs.next()){
                Goods goods = new Goods();
                goods.setBookId(rs.getInt("bookId"));
                goods.setBookName(rs.getString("bookName"));
                goods.setGoodsId(rs.getInt("goodsId"));
                goods.setGoodsImage(rs.getString("goodsImage"));
                goods.setPrice(rs.getDouble("price"));
                goods.setRemain(rs.getInt("remain"));
                goods.setShopId(rs.getInt("shopId"));
                goods.setGoodsName(rs.getString("goodsName"));
                goods.setSell(rs.getInt("sell"));
                list.add(goods);
            }
            DBUtils.close(connection,ps,rs);
            return list;
        }catch (Exception e){
            e.getStackTrace();
            return null;
        }
    }
    public List<Goods> findGoodsByName(String name) {
        List<Goods> goodsList = new ArrayList<Goods>();
        String sql = "SELECT * FROM shopgoods WHERE LOCATE(?,goodsName)>0";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, name);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Goods goods = new Goods();
                goods.setBookId(rs.getInt("bookId"));
                goods.setBookName(rs.getString("bookName"));
                goods.setGoodsId(rs.getInt("goodsId"));
                goods.setGoodsImage(rs.getString("goodsImage"));
                goods.setPrice(rs.getDouble("price"));
                goods.setRemain(rs.getInt("remain"));
                goods.setShopId(rs.getInt("shopId"));
                goods.setGoodsName(rs.getString("goodsName"));
                goods.setSell(rs.getInt("sell"));
                goodsList.add(goods);
            }
            DBUtils.close(connection,ps,rs);
            return goodsList;
        } catch (Exception e) {
            e.getStackTrace();
            return null;
        }

    }
    public boolean deleteGoodsById(int id){
        String sql="DELETE FROM shopgoods WHERE goodsId=?";
        try {
            Connection connection=DBUtils.getConnection();
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.setInt(1,id);
            if(ps.executeUpdate()>0){
                DBUtils.close(connection,ps,null);
                return true;
            }
            DBUtils.close(connection,ps,null);
            return false;
        }catch (Exception e){
            e.getStackTrace();
            return false;
        }
    }
    //修改商品剩余数量，0为+，1为-
    public boolean updateGoodsRemain(int goodsId,int type){
        String sql="UPDATE shopgoods SET remain=remain-1 WHERE goodsId=? AND remain>0";
        String sql2="UPDATE shopgoods SET remain=remain+1 WHERE goodsId=?";
        try{
            Connection connection=DBUtils.getConnection();
            PreparedStatement ps;
            if (type==0) {
               ps= connection.prepareStatement(sql2);
            }else
            ps=connection.prepareStatement(sql);
            ps.setInt(1,goodsId);
            if (ps.executeUpdate()>0){
                DBUtils.close(connection,ps,null);
                return true;
            }
            DBUtils.close(connection,ps,null);
            return false;

        }catch (Exception e){
            e.getStackTrace();
            return false;
        }
    }
    public boolean updateGoodsPriceById(int goodsId,double price){
        String sql="UPDATE shopgoods SET price=? WHERE goodsId=?";
        if (price<0)
            return false;
        try{
            Connection connection=DBUtils.getConnection();
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.setDouble(1,price);
            ps.setInt(2,goodsId);
            if (ps.executeUpdate()>0){
                DBUtils.close(connection,ps,null);
                return true;
            }
            DBUtils.close(connection,ps,null);
            return false;
        }catch (Exception e){
            e.getStackTrace();
            return false;
        }
    }
    public static void main(String[] args){
        GoodsDao dao=new GoodsDao();
//        System.out.println(dao.deleteGoodsById(2));
//        dao.updateGoodsRemain(1,1);
        dao.updateGoodsPriceById(1,66.66);

    }

}
